package com.lsmgc.cms.dao.classify;

import com.lsmgc.cms.entity.classify.Classify;
import com.lsmgc.cms.entity.classify.ClassifyQuery;
import com.lsmgc.cms.util.jdbc.C3P0Utils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang3.StringUtils;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ClassifyDao {

    public int addClassify(Classify classify) throws SQLException {
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "insert into classify(name,img,add_time) value(?,?,?)";
        return runner.update(sql, classify.getName(), classify.getImg(), classify.getAddTime());
    }

    public List<Classify> getClassifyListPage(ClassifyQuery classifyQuery) throws SQLException {
        List<Object> param = new ArrayList<>();
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
        StringBuilder sql = new StringBuilder();
        sql.append("select * from classify");
        if (StringUtils.isNotBlank(classifyQuery.getName())) {
            sql.append(" where");
            sql.append(" name like ?");
            param.add("%" + classifyQuery.getName() + "%");
        }
        sql.append(" order by id desc");
        sql.append(" limit ?,?");
        param.add(classifyQuery.getStartRow());
        param.add(classifyQuery.getPageSize());
        System.out.println("sql: " + sql.toString());
        return runner.query(sql.toString(), new BeanListHandler<>(Classify.class), param.toArray());
    }

    public Long getClassifyListPageCount(ClassifyQuery classifyQuery) throws SQLException {
        List<Object> param = new ArrayList<>();
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
        StringBuilder sql = new StringBuilder();
        sql.append("select count(1) num from classify");
        if (StringUtils.isNotBlank(classifyQuery.getName())) {
            sql.append(" where");
            sql.append(" name like ?");
            param.add("%" + classifyQuery.getName() + "%");
        }
        System.out.println("sql: " + sql.toString());
        return runner.query(sql.toString(), new ScalarHandler<>("num"), param.toArray());
    }

    public List<Classify> getClassifyList(ClassifyQuery classifyQuery) throws SQLException {
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "select * from classify";
        return runner.query(sql, new BeanListHandler<>(Classify.class));
    }

    public Classify getClassifyById(Long id) throws SQLException {
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "select * from classify where id=?";
        return runner.query(sql, new BeanHandler<>(Classify.class), id);
    }

    public int updateClassifyById(Classify classify) throws SQLException {
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "update classify set name=?,img=? where id=?";
        return runner.update(sql, classify.getName(), classify.getImg(), classify.getId());
    }

    public int deleteClassifyById(Long id) throws SQLException {
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "delete from classify where id=?";
        return runner.update(sql, id);
    }
}
